	SELECT	C.DIRECTION, C.START_DT, 
	CASE UPPER(C.DWELLING_TYPE_U)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
	COALESCE(PRODUCT_NM, 'ALL') PRODUCT, 
	COALESCE(CASE C.NATIONAL_ID WHEN 'YES' THEN 'NATIONAL'  WHEN 'NO' THEN 'REGIONAL' END, 'BOTH') LOCATION , 
	COALESCE(C.REGION, 'ALL') REGION,
	YR,
	MN,
	MARKET,
	STATE_CD [STATE],
	AREA_MGR,
	ACCT_MGR,
	PORTFOLIO,
	TRAC_ID,
	PROPERTY_NAME_U,
	QTY		
	FROM			
	( ---C
		SELECT
		 REGION_DESC_U REGION
		,MARKET_AREA_U MARKET
		,STATE STATE_CD
		,AM_U  ACCT_MGR
		,AE_ASSIGNED_U AREA_MGR
		,PORTFOLIO_NAME_U PORTFOLIO
		,STATE||PROPERTY_ID_U TRAC_ID
		,EXTRACT (MONTH FROM POSTDATE_U) MN
		,EXTRACT (YEAR FROM POSTDATE_U) YR
		,CASE 
		 WHEN INDEX(PRODUCT_NM_U,'VOICE') GT 0 THEN 'VOIP' 
		 WHEN INDEX(PRODUCT_NM_U,'HSIA') GT 0 THEN 'HSIA' 
		 ELSE 'IPTV' 
		 END PRODUCT_NM
		,CASE PRODUCT_ACTION_CD_U
		 WHEN 'O' THEN 'OUT'
		 WHEN 'I' THEN 'IN'  
		 END DIRECTION
		,PROD_QTY_U QTY
		,NATIONAL_IND_U NATIONAL_ID
		,DWELLING_TYPE_U
		,PROPERTY_NAME_U
		,POSTDATE_U START_DT			
		FROM
		
		
		( -- X
	
	
			SELECT 
			 REGION_U
			,STATE
			,PROPERTY_ID_U
			,NATIONAL_IND_U
			,DWELLING_TYPE_U
			,REGION_DESC_U
			,PROPERTY_NAME_U
			,AM_U
			,AE_ASSIGNED_U
			,PORTFOLIO_NAME_U
			,MARKET_AREA_U
			,POSTDATE_U
			,PRODUCT_ID_U
			,PRODUCT_NM_U       
			,PRODUCT_ACTION_CD_U  
			,PROD_QTY_U
			 FROM
			(  -- TOT
				SELECT  
			 	CASE  STATE 
				WHEN  'IL'  THEN 'A'
				WHEN  'IN'  THEN 'A'
				WHEN  'MI'  THEN 'A'
				WHEN  'WI'  THEN 'A'
				WHEN  'OH'  THEN 'A'
				WHEN  'CA'  THEN 'P'
				WHEN  'NV'  THEN 'P'
				WHEN  'CT'  THEN 'N'
				WHEN  'TX'  THEN 'S'
				WHEN  'KS'  THEN 'S'
				WHEN  'AR'  THEN 'S'
				WHEN  'OK'  THEN 'S'
				WHEN  'MO'  THEN 'S'
				ELSE 'U'
				END  AS  REGION_U
				,STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
				,BASE.PROPERTY_ID AS PROPERTY_ID_U
				,NATIONAL_IND_U
				,DWELLING_TYPE_U
				,REGION AS REGION_DESC_U
				,PROPERTY_NAME  AS PROPERTY_NAME_U
				,AM  AS AM_U
				,AE_ASSIGNED  AS AE_ASSIGNED_U
				,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
				,MARKET_AREA  AS MARKET_AREA_U
				,POSTDATE  AS POSTDATE_U
				,BASE.PRODUCT_ID   AS PRODUCT_ID_U
				,PRODUCT_NM           AS PRODUCT_NM_U       
				,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
				,PROD_QTY AS PROD_QTY_U
				FROM
				( -- LEGS
					SELECT  
					 PROPERTY_ID 
					,REGION
					,PROPERTY_NAME
					,NATIONAL_IND  AS  NATIONAL_IND_U
					,DWELLING_TYPE  AS DWELLING_TYPE_U
					,AM
					,AE_ASSIGNED
					,PORTFOLIO_NAME
					,MARKET_AREA
					,POSTDATE
					,PRODUCT_ID   
					,PRODUCT_ACTION_CD             
					,PROD_QTY  
					,STATE   
					FROM
					( -- CHK
						SELECT 
						 S.PROPERTY_ID 
						,SC.REGION
						,SC.PROPERTY_NAME
						,SC.NATIONAL_IND
						,SC.DWELLING_TYPE
						,SC.AM
						,SC.AE_ASSIGNED
						,SC.PORTFOLIO_NAME
						,SC.MARKET_AREA
						,E.EVENT_ID
						,E.POSTDATE
						,E.STATE
						FROM 
						SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
						S
						,SMARTMOVES.TXSM101_SO_SCORE 	
						SC
						,(	-- E		  		
				  			SELECT 
							EVENT_ID
							,POSTDATE
							,ADDRESS
							,STATE
							,ZIP_CD					
							FROM
							( -- BASE
								SELECT	
								EVENT_ID
								,POSTDATE
								,SECONDARY_ADDRESS_TXT AS  UNIT
								,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
						         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
						             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
						                  TRIM(C.STREET_NM)                          
						             ELSE C.STREET_NM                                
						             END (NAMED ADR1)                                
						         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
						             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
						             ELSE ADR1                                            
						             END (NAMED ADR2)                                     
						         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
						             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
						             ELSE ADR2                                            
						             END (NAMED ADR3)                                     
						         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
						             THEN TRIM(ADR3) ||' '||                              
						                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
						             ELSE ADR3                                            
						             END (NAMED ADDRESS)      
								,C.ZIP_CD
								,C.TERRITORY_CD AS STATE
							    FROM  
							 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
								C  
								,
								( --S
									SELECT 
		  							 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
							 		,H205.EVENT_ID
									,H205.SERVICE_ORDER_NBR        		SONBR
									,H205.EVENT_POSTED_DT    			POSTDATE
									,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
									FROM
									 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
									 ,( --O
									 		SELECT 
									 		 O.ORDER_ID	
											,O.SERVICE_ORDER_CD
											,SALES_CHANNEL_CD
										  	FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
										  	O					
											LEFT OUTER JOIN 
											( --D
												SELECT 
												 A.ORDER_ID
												,A.ORDER_ACTION_TYPE_CD
												FROM
												TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
												A 
										)
										D
									   	ON O.ORDER_ID = D.ORDER_ID
									)
									O	---------------------------------------------------------------------------------------------------------------------------------------------------------------												
									WHERE   
									H205.EVENT_POSTED_DT > DATE-369 
									AND  H205.DATA_SOURCE_CD IN (150)
									AND O.ORDER_ID = SONBR
									AND RAAID > 0
								) 
								S
								WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
		                        AND C.ZIP_CD <> ' '
								AND C.ADDRESS_USAGE_CD = 'S' 
							) 
							BASE
						) 
						E
						WHERE 
					    S.ZIP = E.ZIP_CD
						AND S.PROPERTY_ID = SC.PROPERTY_ID
						AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
						AND TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
					) 
					CHK
					LEFT OUTER JOIN 
					( --ACT
						SELECT 
						 D.EVENT_ID
						,D.PRODUCT_ID                    
						,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
						,SUM(D.PRODUCT_QTY)    PROD_QTY                              
						FROM 
						ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
						D
						WHERE  D.DATA_SOURCE_CD IN (150)
						AND D.INWARD_OUTWARD_CD  IN (1,4)
						AND D.PRODUCT_ID > 0
						GROUP BY 1,2,3
					) 
					ACT
					ON CHK.EVENT_ID = ACT.EVENT_ID
				)
				BASE
				LEFT OUTER JOIN	
				( --P
					SELECT 
					 PRODUCT_ID
					,PRODUCT_NM
					FROM
					ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
					WHERE PRODUCT_LAST_EFFECTIVE_DT > DATE
					AND PRODUCT_ID > 0
				)  
				P
				ON BASE.PRODUCT_ID = P.PRODUCT_ID
			) 
			TOT					
	
			) 
			X	--ACC_BASE_DATA		
	)
	C
